package com.simple.utils;

import cn.hutool.core.date.DateUtil;
import com.simple.dto.BillExcelDTO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * @ClassName ExcelUtil
 * @Author SimpleTree
 **/
@Slf4j
public class ExcelUtil {


    public static void readExcel(String path) {
        readExcel(path, -1);
    }

    public static void readExcel(String path, Integer sheetAtIndex) {
        readExcel(path,sheetAtIndex,0,-1,-1);
    }

    public static List<BillExcelDTO> readExcel(String path, Integer sheetAtIndex, Integer startColumn, Integer endColumn, Integer endRow) {
        List<BillExcelDTO> billExcelDTOS = new ArrayList<>();
        try {
            File file = new File(path);
            if (!file.exists()) {
                throw new Exception("文件不存在!");
            }
            XSSFWorkbook sheets = (XSSFWorkbook) WorkbookFactory.create(new File(path));

            // 读取整个Excel
            // 获取第一个表单Sheet
            XSSFSheet sheetAt = sheets.getSheetAt(sheetAtIndex == -1 ? 0 : sheetAtIndex);
            String month = sheets.getSheetName(sheetAtIndex);
            month = month.length() < 2 ? "0" + month : month;
            int year = DateUtil.year(new Date());

            int physicalNumberOfRows = endRow == -1 ? sheetAt.getPhysicalNumberOfRows() : endRow;

            String day = "";

            //默认第一行为标题行，i = 0
            XSSFRow titleRow = sheetAt.getRow(0);
            // 循环获取每一行数据
            for (int i = 1; i < physicalNumberOfRows - 1; i++) {
                XSSFRow row = sheetAt.getRow(i);
                if (row == null) {
                    continue;
                }

                BillExcelDTO billExcelDTO = new BillExcelDTO();

                int physicalNumberOfCells = endColumn == -1 ? row.getPhysicalNumberOfCells() : endColumn;
                for (int index = startColumn; index < physicalNumberOfCells; index++) {
                    XSSFCell titleCell = titleRow.getCell(index);
                    XSSFCell cell = row.getCell(index);
                    if (cell == null) {
                        continue;
                    }
                    cell.setCellType(CellType.STRING);
                    if ("日期".equals(titleCell.getStringCellValue())) {
                        String cellValue = cell.getStringCellValue();

                        if (cellValue.contains(".")) {
                            String[] split = cellValue.split("\\.");

                            billExcelDTO.setDate(year + "-"
                                    + (split[0].length() == 1 ? "0" + split[0] : split[0]) + "-"
                                    + (split[1].length() == 1 ? "0" + split[1] : split[1]) );
                        }else {
                            if (StringUtils.isEmpty(cellValue)) {
                                cellValue = day;
                            } else {
                                day = cellValue;
                            }
                            cellValue = cellValue.length() < 2 ? "0" + cellValue : cellValue;
                            billExcelDTO.setDate(year + "-" + month + "-" + cellValue);
                        }

                    } else if (titleCell.getStringCellValue().equals("消费")) {
                        billExcelDTO.setCostContent(cell.getStringCellValue());
                    } else if (titleCell.getStringCellValue().equals("金额")) {
                        billExcelDTO.setCostMoney(Double.parseDouble(cell.getStringCellValue()));
                    } else {
                        continue;
                    }
                }
                billExcelDTOS.add(billExcelDTO);
            }

        } catch (Exception e) {
            log.info("解析文件发生异常：{}",e);
        }
        return billExcelDTOS;
    }

    public static void writeExcel(List<String> res) {

        List<String> titles = Arrays.asList("标题1", "标题2");
        List<String> secondTitles = Arrays.asList("标题1", "副标题1", "副标题2");

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("工作簿名称");
        CellStyle cellStyle = getCellStyle(workbook);

        //第一行标题
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < titles.size(); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(titles.get(i));
        }

        //第二行标题
        row = sheet.createRow(1);
        for (int i = 0; i < secondTitles.size(); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(titles.get(i));
        }

        //合并标题
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));

        int index = 2;

        for (String s : res) {
            List<String> values = new ArrayList<>();
            int first = index;

            if ("这里为空".equals(s)) {
                row = sheet.createRow(index);

                values.add("");
                values.add("");
                values.add("");

                for (int i = 0; i < values.size(); i++) {
                    XSSFCell cell = row.createCell(i);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(titles.get(i));
                }
            } else {
                for (int k = 0; k < 3; k++) {
                    values.add("第一行");
                    values.add("第二行");
                    values.add("第三行");
                    for (int i = 0; i < values.size(); i++) {
                        XSSFCell cell = row.createCell(i);
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(titles.get(i));
                    }
                    index++;
                }
                sheet.addMergedRegion(new CellRangeAddress(first, index - 1, 0, 0));
            }
        }

        writeExcelToFile(workbook, "D:\\");

    }

    public static CellStyle getCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));

        cellStyle.setWrapText(true);

        Font font = workbook.createFont();
        font.setBold(false);
        cellStyle.setFont(font);

        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setBorderBottom(BorderStyle.MEDIUM);
        cellStyle.setBorderTop(BorderStyle.MEDIUM);
        cellStyle.setBorderLeft(BorderStyle.MEDIUM);
        cellStyle.setBorderRight(BorderStyle.MEDIUM);
        return cellStyle;
    }

    public static void writeExcelToFile(XSSFWorkbook workbook, String path) {
        File file = new File(path);
        OutputStream os = null;
        try {
            os = new FileOutputStream(file);
            workbook.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static void main(String[] args) {
        //String path = "C:\\Users\\xzc09\\Desktop\\ty测试.xlsx";
        String path = "C:\\Users\\xzc09\\Desktop\\测试.xlsx";
        //String path = "C:\\Users\\xzc09\\Desktop\\熊镇川记账本.xlsx";
        List<BillExcelDTO> billExcelDTOS = readExcel(path, 0, 0, -1, -1);

        BigDecimal chifan = new BigDecimal("0");
        BigDecimal lingshi = new BigDecimal("0");
        BigDecimal meituan = new BigDecimal("0");
        BigDecimal duoduo = new BigDecimal("0");
        BigDecimal qqk = new BigDecimal("0");
        BigDecimal cf = new BigDecimal("0");
        BigDecimal dacan = new BigDecimal("0");
        BigDecimal other = new BigDecimal("0");
        BigDecimal total = new BigDecimal("0");

        for (BillExcelDTO billExcelDTO : billExcelDTOS) {
            String content = billExcelDTO.getCostContent();
            String money = billExcelDTO.getCostMoney() + "";
            total = total.add(new BigDecimal(money));
            if (content.contains("饭")) {
                if (billExcelDTO.getCostMoney() > 100) {
                    dacan = dacan.add(new BigDecimal(money));
                } else {
                    chifan = chifan.add(new BigDecimal(money));
                }
            } else if (content.contains("美团")) {
                meituan = meituan.add(new BigDecimal(money));
            } else if (content.contains("多多")) {
                duoduo = duoduo.add(new BigDecimal(money));
            } else if (content.contains("绝味") || content.contains("臭豆腐") || content.contains("茶")) {
                lingshi = lingshi.add(new BigDecimal(money));
            }else if (content.contains("车费")) {
                cf = cf.add(new BigDecimal(money));
            } else if (content.contains("亲情卡")) {
                qqk = qqk.add(new BigDecimal(money));
            } else {
                other = other.add(new BigDecimal(money));
            }
        }


        System.out.println("吃饭花费金额:" + chifan.doubleValue() +  "\t占比： " + chifan.divide(total,3, RoundingMode.FLOOR));
        System.out.println("零食花费金额:" + lingshi.doubleValue() +  "\t占比： " + lingshi.divide(total,3, RoundingMode.FLOOR));
        System.out.println("美团花费金额:" + meituan.doubleValue() +  "\t占比： " + meituan.divide(total,3, RoundingMode.FLOOR));
        System.out.println("多多花费金额:" + duoduo.doubleValue() +  "\t占比： " + duoduo.divide(total,3, RoundingMode.FLOOR));
        System.out.println("亲情卡花费金额:" + qqk.doubleValue() +  "\t占比： " + qqk.divide(total,3, RoundingMode.FLOOR));
        System.out.println("车费花费金额:" + cf.doubleValue() +  "\t占比： " + cf.divide(total,3, RoundingMode.FLOOR));
        System.out.println("大餐(吃饭超过100)花费金额:" + dacan.doubleValue() +  "\t占比： " + dacan.divide(total,3, RoundingMode.FLOOR));
        System.out.println("其他花费金额:" + other.doubleValue() +  "\t占比： " + other.divide(total,3, RoundingMode.FLOOR));

    }

}
